﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SaunaServer.Model.Logger;
using System.Net;
using System.Net.Security;
using System.IO;
using System.Net.Sockets;
using System.Threading;
using MySql.Data.MySqlClient;
using SaunaServer.Model.Network;

namespace SaunaServer.Model.Database
{
    /// <summary>
    /// TCP/IP DAO Communication Class
    /// </summary>
    /// <dates>
    /// 28/09/2011 - created by Chiaki Sato
    /// </dates>
    /// <remarks>
    /// Note:
    /// This class is thread safe. 
    /// </remarks>
    public class DBDaoGateway : DBBaseSetting, IDBDaoGateway
    {
        // Constant
        private const string FILE_LOGGER = "FileLogger";
        private const string CONNECTION_STRING = "server={0};user id={1};Password={2};database={3};persist security info=False";
        private const string SQL_QUERY_USER = @"SELECT * FROM users WHERE userid = '{0}' and password = '{1}';";
        private const string SQL_QUERY_TEST = @"SELECT * FROM users;";
        private const string SQL_INSERT_DOWNLOAD_LOG = @"insert into downloadlog (userid, filename, datetime) value ('{0}', '{1}', '{2}');";
        private const string SQL_POPULAR_FILE_RANKING = @"select filename, count(*) as count from downloadlog group by filename order by count desc";
        private const string FIELD_FIRST_NAME = "firstname";
        private const string FIELD_LAST_NAME = "lastname";
        private const string FIELD_USER_ID = "userid";
        private const string FIELD_FILE_NAME = "filename";
        private const string FIELD_DOWNLOAD_COUNT = "count";
        private const string FIELD_DELIMITER = " ,";
        private const int LIST_MAX = 30;
        private const int TOTAL_COLUMN_NUMBER = 3;
        private const int INDEX_RANKING_NUMBER = 0;
        private const int INDEX_FILE_NAME = 1;
        private const int INDEX_DOWNLOAD_COUNT = 2;

       
        // Private variables
        private object lockObject;
        private LoggerFactoryImplementation loggerFactory;
        private ILogger fileLogger;
 
        // Private static singleton
        private static DBDaoGateway dbDaoGatewaySingleton = new DBDaoGateway();

        /// <summary>
        /// Private constructor for singleton
        /// </summary>
        private DBDaoGateway()
        {
            lockObject = new object();
        }

        /// <summary>
        /// Get singleton instance
        /// </summary>
        public static DBDaoGateway Instance(NetworkSetting networkSetting)
        {
            // Initialize Ftp Setting
            dbDaoGatewaySingleton.DatabaseName = networkSetting.DBDatabaseName;
            dbDaoGatewaySingleton.HostAddress = networkSetting.DBHostAddress;
            dbDaoGatewaySingleton.UserName = networkSetting.DBUserName;
            dbDaoGatewaySingleton.Password = networkSetting.DBPassword;
            dbDaoGatewaySingleton.LogFileDirectory = networkSetting.LogFileDirectory;
            dbDaoGatewaySingleton.loggerFactory = new LoggerFactoryImplementation();
            dbDaoGatewaySingleton.fileLogger = dbDaoGatewaySingleton.loggerFactory.Make(FILE_LOGGER);

            return dbDaoGatewaySingleton;
        }

        /// <summary>
        /// AuthenticateUser
        /// </summary>
        /// <param name="userId">User Id</param>
        /// <param name="password">Password</param>
        /// <param name="userFullName">User full name</param>
        public bool AuthenticateUser(string userId, string password, out string userFullName)
        {
            userFullName = "";
            bool isUserAuthenticationSuccess = false;

            // Connect to db
            MySqlConnection mySqlConnection = null;
            string connctionString = String.Format(CONNECTION_STRING, HostAddress, UserName, Password, DatabaseName);
            mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(connctionString);

            // Create Command
            MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();

            // Set connection
            msqlCommand.Connection = mySqlConnection;

            // Create SQL query string
            msqlCommand.CommandText = String.Format(SQL_QUERY_USER, userId, password);

            try
            {
                // Open the connection
                mySqlConnection.Open();

                // Use a DataReader to process each record
                MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
                userFullName = "";
                while (msqlReader.Read())
                {
                    userFullName = msqlReader[FIELD_FIRST_NAME] + " " + msqlReader[FIELD_LAST_NAME];
                    if (userFullName == " ")
                    {
                        userFullName = (string)msqlReader[FIELD_USER_ID];
                    }

                    isUserAuthenticationSuccess = true;

                    Console.WriteLine(msqlReader[FIELD_USER_ID] + FIELD_DELIMITER
                                    + msqlReader[FIELD_FIRST_NAME] + FIELD_DELIMITER 
                                    + msqlReader[FIELD_LAST_NAME]);
                    
                    // Exit after one user account
                    break;
                }

            }
            catch (Exception e)
            {
                isUserAuthenticationSuccess = false;
                Console.WriteLine(e);
            }
            finally
            {
                // Close the connection
                mySqlConnection.Close();
            }

            return isUserAuthenticationSuccess;
        }

        /// <summary>
        /// Create Download Log record
        /// </summary>
        /// <param name="userId">User Id</param>
        /// <param name="fileName">File Name</param>
        /// <param name="dateTime">Download date and time</param>
        public void CreateDownloadLogRecord(string userId, string fileName, string dateTime)
        {

            // Connect to db
            MySqlConnection mySqlConnection = null;
            string connctionString = String.Format(CONNECTION_STRING, HostAddress, UserName, Password, DatabaseName);
            mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(connctionString);

            // Create Command
            MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();

            // Set connection
            msqlCommand.Connection = mySqlConnection;

            // Create SQL query string
            msqlCommand.CommandText = String.Format(SQL_INSERT_DOWNLOAD_LOG, userId, fileName, dateTime);

            try
            {
                // Open the connection
                mySqlConnection.Open();

                // Use a DataReader to process each record
                msqlCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                // Close the connection
                mySqlConnection.Close();
            }

        }

        /// <summary>
        /// Get Popular File Ranking
        /// <returns>Popular File Ranking List</returns>
        /// </summary>
        public string[,] GetPopularFileRanking()
        {            
            string[,] popularFileRanking = new string[LIST_MAX, TOTAL_COLUMN_NUMBER];

            // Connect to db
            MySqlConnection mySqlConnection = null;
            string connctionString = String.Format(CONNECTION_STRING, HostAddress, UserName, Password, DatabaseName);
            mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(connctionString);

            // Create Command
            MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();

            // Set connection
            msqlCommand.Connection = mySqlConnection;

            // Create SQL query string
            msqlCommand.CommandText = SQL_POPULAR_FILE_RANKING;

            try
            {
                // Open the connection
                mySqlConnection.Open();

                // Use a DataReader to process each record
                MySqlDataReader msqlReader = msqlCommand.ExecuteReader();

                int i = 0;
                while (msqlReader.Read())
                {
                    popularFileRanking[i, INDEX_RANKING_NUMBER] = (i+1).ToString();
                    popularFileRanking[i, INDEX_FILE_NAME] = (string)msqlReader[FIELD_FILE_NAME];
                    long downloadCount = (long)msqlReader[FIELD_DOWNLOAD_COUNT];
                    popularFileRanking[i, INDEX_DOWNLOAD_COUNT] = downloadCount.ToString();

                    i++;
                    if (i > LIST_MAX -1)
                    {
                        break;
                    }
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                // Close the connection
                mySqlConnection.Close();
            }

            return popularFileRanking;

        }

        /// <summary>
        /// Test Database Connection
        /// </summary>
        public void TestConnection()
        {
            // Connect to db
            MySqlConnection mySqlConnection = null;

            try
            {
                string connctionString = String.Format(CONNECTION_STRING, HostAddress, UserName, Password, DatabaseName);
                mySqlConnection = new MySql.Data.MySqlClient.MySqlConnection(connctionString);

                // Create Command
                MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();

                // Set connection
                msqlCommand.Connection = mySqlConnection;

                // Create SQL query string
                msqlCommand.CommandText = String.Format(SQL_QUERY_TEST);

                // Open the connection
                mySqlConnection.Open();

                // Use a DataReader to process each record
                MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
                while (msqlReader.Read())
                {
                    // Exit after one user account
                    break;
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw new Exception(e.ToString());
            }
            finally
            {
                // Close the connection
                if (mySqlConnection != null)
                {
                    mySqlConnection.Close();
                }
            }
        }    
    }
}
